/*
 * TransactionDAO.java
 *
 * Created on March 5, 2007, 4:18 PM
 *
 * To change this template, choose Tools | Template Manager
 * and open the template in the editor.
 */

package edu.byu.isys413.jjenkins.data;

import java.sql.Connection;
import java.sql.Date;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.LinkedList;
import java.util.List;

 /**
 * This class contains methods to create, read, update and delete the 
 * transaction Object. It also contains methods to read an object by
 * serveral different criteria. All interaction between the object 
 * and the database is done through this class. 
 * 
 * @author      Jeff Jenkins
 * @version     1.0
 * 
 */
public class TransactionDAO {
    
    private static TransactionDAO instance = null;
    
    /** Creates a new instance of TransactionDAO */
    private TransactionDAO() {
    }
    
        /* This method implements the singleton pattern. It insures that only
         * on instance of TransactionDAO exists
         *
         * @return: TransactionDAO instance */
    
    public static synchronized TransactionDAO getInstance() {
        if (instance == null) {
            instance = new TransactionDAO();
        }
        return instance;
    }
    
    
    /* This method creates a new TransactionBO, creates and sets the GUID, and
     * returns it to the calling method.
     *
     * @return: TransactionBO
     * @throws: DataException
     *
     */
    
    public TransactionBO create() throws DataException {
        try {
            String id = GUID.generate();
            TransactionBO transaction = new TransactionBO();
            transaction.setId(id);
            Cache.getInstance().put(id, transaction);
            return transaction;
            
            
        } catch (Exception e) {
            throw new DataException("Could not create GUID", e);
        }
        
    }
    
    
    /**
     * This is the public read statement.  It loads an existing record
     * from the database.
     *
     * @Par: String: id
     * @Return: TransactionBO
     * @Throws: DataException
     */
    public synchronized TransactionBO read(String id) throws DataException {
        
        // Checks to see if the item is in cache
        Cache cache = Cache.getInstance();
        TransactionBO transaction = (TransactionBO)cache.get(id);
        
        try {
            
            // Gets connection
            ConnectionPool connPool = ConnectionPool.getInstance();
            Connection conn = connPool.get();
            
            // If the item is not in cache a connection is checked out and
            // The item is read from the database.
            if(transaction == null){
                try {
                    //reads in customer
                    transaction = read(id, conn);
                    
                }catch (SQLException e) {
                    conn.rollback();
                    throw new DataException("Could not retrieve record for id=" + id, e);
                    
                }
            }
            
            // After all items are read the
            // connection committs and is returned.
            conn.commit();
            connPool.release(conn);
            
            
            
        } catch (SQLException e) {
            throw new DataException("Could not retrieve record for id=" + id, e);
        } catch (DataException e) {
            throw new DataException("Could not retrieve record for id=" + id, e);
        } catch (ConnectionPoolException e) {
            throw new DataException("Could not retrieve record for id=" + id, e);
        }
        
        // Return Object
        return transaction;
    }
    
    /**
     *  This is a package method that is called by the public read (above) or
     *  by another DAO.  Either way we already have a connection to the database
     *  to use.  The user (controller) code never calls this one directly, since
     *  it can't know about Connection objects or SQLExceptions.
     *
     *  @Par: String: id, Connection conn
     *  @Return: TransactionBO
     *  @throws: DataException, SQLException
     */
    synchronized TransactionBO read(String id, Connection conn) throws SQLException, DataException {
        
        
        // Checks to see if the item is in cache
        
        Cache cache = Cache.getInstance();
        TransactionBO transaction = (TransactionBO)cache.get(id);
        
        // Quieries that database
        if(transaction == null){
            
            PreparedStatement pstmt = conn.prepareStatement("SELECT * FROM transactions WHERE transaction_id = ?");
            pstmt.setString(1, id);
            pstmt.execute();
            ResultSet rs = pstmt.getResultSet();
            
            if (rs.next()) {
                transaction = new TransactionBO();
                transaction.setId(rs.getString("transaction_id"));
                transaction.setDate(rs.getDate("trans_date"));
                transaction.setStatus(rs.getString("status"));
                transaction.setType(rs.getString("transaction_type"));
                transaction.setCurrentOpts(CurrentOptsDAO.getInstance().read(rs.getString("current_opts"), conn));
                transaction.setSubtotal(rs.getDouble("subtotal"));
                transaction.setTax(rs.getDouble("tax"));
                transaction.setTotal(rs.getDouble("total"));
                transaction.setTax_rate(rs.getDouble("tax_rate"));
                String otran = rs.getString("original_transaction");
                if(otran != null){
                    transaction.setOriginalTrans(TransactionDAO.getInstance().read(otran,conn));
                }
                transaction.setCustomer(CustomerDAO.getInstance().read(rs.getString("customer"),conn));
                transaction.setChange(rs.getDouble("change"));
                
                
                // Reads transaction lines
                transaction.setTransactionList(TransactionLineDAO.getInstance().readByTransaction(transaction, conn));
                
                // Reads payment
                transaction.setPayment(PaymentDAO.getInstance().readByTransaction(transaction.getId(), conn));
                
                // If the payment is not null it sets the transaction in the payment
                if(transaction.getPayment() != null){
                    transaction.getPayment().setTransaction(transaction);
                }
                
                // Sets already in database
                transaction.setObjectAlreadyInDB(true);
                
                // adds to cache
                cache.put(transaction.getId(), transaction);
                
            }else{
                throw new DataException("Object was not found in the database.");
            }
        }
        
        
        // returns object
        return transaction;
    }
    
    
    
    /**
     * This is the public save method.  It is what is called when
     * the user (controller) code wants to save or update an object
     * into the database.
     *
     * @par TransactionBO transaction
     * @throws DataExcpetion
     */
    public synchronized void save(TransactionBO transaction) throws DataException {
        try {
            
            
            // retrieve a database connection from the pool
            ConnectionPool connPool = ConnectionPool.getInstance();
            
            Connection conn = connPool.get();
            
            
            try {
                
                // Saves Transaction
                save(transaction, conn);
                
            }catch (SQLException e) {
                conn.rollback();
                throw new DataException("Could not save record for" + transaction.getId(), e);
            }
            
            // After all items are saved the
            // connection committs and is returned.
            conn.commit();
            connPool.release(conn);
            
            // touch the cache for the object
            Cache.getInstance().touch(transaction.getId());
            
            
            
            
        } catch (DataException e) {
            throw e;
            
        } catch (ConnectionPoolException e) {
            throw new DataException("Could not save record for" + transaction.getId(), e);
        } catch (SQLException e) {
            throw new DataException("Could not save record for" + transaction.getId(), e);
        }
        
        
    }
    
    /**
     *  This is a package method that is called by the public save (above) or
     *  by another DAO.  Either way we already have a connection to the database
     *  to use.  The user (controller) code never calls this one directly, since
     *  it can't know about Connection objects or SQLExceptions.
     *
     *  By having other DAOs call this save method (rather than update or
     *  insert below, each DAO in a chained save (like the CustomerDAO calling
     *  MembershipDAO calling InterestDAO save chain) can independently decide
     *  whether to udpate or insert the BO it's saving.  That's why I made
     *  update and insert private rather than package level -- if you call
     *  them directly from another DAO, this DAO can't decide whether it's
     *  object needs to be inserted or updated.
     *
     *  @par: TransactionBO Connection conn
     *  @throws: SQLException, DataException
     */
    synchronized void save(TransactionBO transaction, Connection conn) throws SQLException, DataException {
        
        // check the dirty flag in the object.  if it is dirty,
        // run update or insert
        if (transaction.isDirty()) {
            if (transaction.isObjectAlreadyInDB()) {
                if(transaction.getStatus() != "finished" || transaction.getStatusChanged()){ // you're not allowed to update once the transaction is finished
                    update(transaction, conn);
                    
                    // Save transaction lines
                    List lines = transaction.getTransactionList();
                    if(lines.size() > 0){
                        TransactionLineDAO.getInstance().saveAllTransactionLines(lines, conn);
                    }
                    
                    //Save payment
                    // You'll never update a payment
                    
                }else{
                    conn.rollback();
                    throw new DataException("You cannot update a transaction once the status is finished");
                }
            }else{
                insert(transaction, conn);
                
                // Save transaction lines
                List lines = transaction.getTransactionList();
                if(lines.size() > 0){
                    TransactionLineDAO.getInstance().saveAllTransactionLines(lines, conn);
                }
                
                //Save payment
                PaymentBO payment = transaction.getPayment();
                if(payment != null){
                    PaymentDAO.getInstance().save(payment, conn);
                }
                
            }
            transaction.setDirty(false);
            transaction.setStatusChanged(false);
        }
        
        
        
        
        
    }
    
    /**
     * This method is really a part of save(bo, conn) above.  It could be
     * embedded directly in it, but I've separated it into it's own method
     * to isolate the SQL udpate statement and make it more readable.  But
     * logically, it's really just a part of save.
     *
     * @Par TransactionBO
     */
    private synchronized void update(TransactionBO transaction, Connection conn) throws SQLException, DataException {
        
        PreparedStatement pstmt = conn.prepareStatement("UPDATE transactions SET transaction_id = ?, trans_date = ?, status = ?, transaction_type = ?, current_opts = ?, subtotal = ?, tax = ?, total = ?, tax_rate = ?, original_transaction = ?, customer = ?, change = ? WHERE transaction_id = ?");
        pstmt.setString(1, transaction.getId());
        pstmt.setDate(2, transaction.getDate());
        pstmt.setString(3, transaction.getStatus());
        pstmt.setString(4, transaction.getType());
        pstmt.setString(5, transaction.getCurrentOptsID());
        pstmt.setDouble(6, transaction.getSubtotal());
        pstmt.setDouble(7, transaction.getTax());
        pstmt.setDouble(8, transaction.getTotal());
        pstmt.setDouble(9, transaction.getTax_rate());
        pstmt.setString(10, transaction.getOriginalTransID());
        pstmt.setString(11, transaction.getCustomerID());
        pstmt.setDouble(12, transaction.getChange());
        pstmt.setString(13, transaction.getId());
        
        
        pstmt.execute();
        
    }
    
    /**
     * This method is really a part of save(bo, conn) above.  It could be
     * embedded directly in it, but I've separated it into it's own method
     * to isolate the SQL insert statement and make it more readable.  But
     * logically, it's really just a part of save.
     *
     * @Par CustomerBO customer
     */
    private synchronized void insert(TransactionBO transaction, Connection conn) throws SQLException, DataException {
        
        // Creates prepared statemetn
        PreparedStatement pstmt = conn.prepareStatement("INSERT INTO transactions VALUES(?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)");
        pstmt.setString(1, transaction.getId());
        pstmt.setDate(2, transaction.getDate());
        pstmt.setString(3, transaction.getStatus());
        pstmt.setString(4, transaction.getType());
        pstmt.setString(5, transaction.getCurrentOptsID());
        pstmt.setDouble(6, transaction.getSubtotal());
        pstmt.setDouble(7, transaction.getTax());
        pstmt.setDouble(8, transaction.getTotal());
        pstmt.setDouble(9, transaction.getTax_rate());
        pstmt.setString(10, transaction.getOriginalTransID());
        pstmt.setString(11, transaction.getCustomerID());
        pstmt.setDouble(12, transaction.getChange());;
        pstmt.execute();
        
        //Sets already in database true
        transaction.setObjectAlreadyInDB(true);
        
    }
    
    
    /* Transactions cannot be deleted. They an only be set to void if not finished */
    
    public synchronized void delete(TransactionBO line)throws DataException{
        
        throw new DataException("You can not delete a transaction. You must void the transaction");
        /* Transactions cannot be deleted. They an only be set to void if not finished */
    }
    
    
    
    
    /**
     *  This method reads all transactions lines for a customer
     *
     *  @Par: String: id, Connection conn
     *  @Return: List<TransactionBO>
     *  @throws: DataException, SQLException
     */
    synchronized List<TransactionBO> readByTransaction(String id, Connection conn) throws SQLException, DataException {
        
        List transactions = new LinkedList();
        
        // prepares statemnt
        
        PreparedStatement pstmt = conn.prepareStatement("SELECT transaction_id FROM transactions WHERE customer = ?");
        pstmt.setString(1, id);
        pstmt.execute();
        ResultSet rs = pstmt.getResultSet();
        
        
        while(rs.next()){
            String line_id = rs.getString("transaction_id");
            transactions.add(read(line_id, conn));
        }
        
        return transactions;
    }
    
}
